package model.tablemodel;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import util.SortUtils;
import database.DBConnection;

public class StavkaPrometnogDokumentaTableModel extends AbstractTableModel {

	private static final long serialVersionUID = 1L;

	public StavkaPrometnogDokumentaTableModel(Object[] colNames, int rowCount) {
		super(colNames, rowCount);
		basicQuery = "SELECT SIFRA_PREDUZECA, POSLOVNA_GODINA, BROJ_PROMETNOG_DOK, DATUM_NASTANKA_PROM_DOK, "
				+ "REDNI_BROJ_ST_PROM_DOK, SIFRA_GRUPE, SIFRA_ARTIKLA, KOLICINA, CENA, VREDNOST FROM STAVKA_PROMETNOG_DOKUMENTA";
		orderBy = " ORDER BY SIFRA_PREDUZECA, POSLOVNA_GODINA, BROJ_PROMETNOG_DOK, DATUM_NASTANKA_PROM_DOK, "
				+ "REDNI_BROJ_ST_PROM_DOK";
	}

	@Override
	protected void fillData(String sql) throws SQLException {
		// TODO Auto-generated method stub
		setRowCount(0);
		Statement stmt = DBConnection.getConnection().createStatement();
		ResultSet rset = stmt.executeQuery(sql);
		while (rset.next()) {
			String sifraPreduzeca = rset.getString("SIFRA_PREDUZECA");
			String poslovnaGodina = rset.getString("POSLOVNA_GODINA");
			String brojPrometnogDok = rset.getString("BROJ_PROMETNOG_DOK");
			String datumNastankaPromDok = rset
					.getString("DATUM_NASTANKA_PROM_DOK");
			String redniBrojStPromDok = rset
					.getString("REDNI_BROJ_ST_PROM_DOK");
			String sifraGrupe = rset.getString("SIFRA_GRUPE");
			String sifraArtikla = rset.getString("SIFRA_ARTIKLA");
			String kolicina = rset.getString("KOLICINA");
			String cena = rset.getString("CENA");
			String vrednost = rset.getString("VREDNOST");
			addRow(new String[] { sifraPreduzeca, poslovnaGodina,
					brojPrometnogDok, datumNastankaPromDok, redniBrojStPromDok,
					sifraGrupe, sifraArtikla, kolicina, cena, vrednost });
		}
		rset.close();
		stmt.close();
		fireTableDataChanged();

	}

	@Override
	public void deleteRow(int index) throws SQLException {
		// TODO Auto-generated method stub
		PreparedStatement stmt = DBConnection
				.getConnection()
				.prepareStatement(
						"DELETE FROM STAVKA_PROMETNOG_DOKUMENTA WHERE SIFRA_PREDUZECA=? AND POSLOVNA_GODINA=? AND BROJ_PROMETNOG_DOK=? AND "
								+ "DATUM_NASTANKA_PROM_DOK=? AND REDNI_BROJ_ST_PROM_DOK=?");
		String sifraPreduzeca = (String) getValueAt(index, 0);
		String poslovnaGodina = (String) getValueAt(index, 1);
		String brojPrometnogDok = (String) getValueAt(index, 2);
		String datumNastankaPromDok = (String) getValueAt(index, 3);
		String redniBrojStPromDok = (String) getValueAt(index, 4);
		stmt.setString(1, sifraPreduzeca);
		stmt.setString(2, poslovnaGodina);
		stmt.setString(3, brojPrometnogDok);
		stmt.setString(4, datumNastankaPromDok);
		stmt.setString(5, redniBrojStPromDok);
		// Brisanje iz baze
		int rowsAffected = stmt.executeUpdate();
		stmt.close();
		DBConnection.getConnection().commit();
		if (rowsAffected > 0) {
			// i brisanje iz TableModel-a
			removeRow(index);
			fireTableDataChanged();
		}
	}

	@Override
	protected int sortedInsert(String[] cols) {
		// TODO Auto-generated method stub
		int left = 0;
		int right = getRowCount() - 1;
		int mid = (left + right) / 2;
		while (left <= right) {
			mid = (left + right) / 2;
			String aSifra = (String) getValueAt(mid, 0);
			if (SortUtils.getLatCyrCollator().compare(cols[0], aSifra) > 0)
				left = mid + 1;
			else if (SortUtils.getLatCyrCollator().compare(cols[0], aSifra) < 0)
				right = mid - 1;
			else {
				// ako su jednaki: to ne moze da se desi ako tabela ima primarni
				// kljuc
				aSifra = (String) getValueAt(mid, 1);
				if (SortUtils.getLatCyrCollator().compare(cols[1], aSifra) > 0)
					left = mid + 1;
				else if (SortUtils.getLatCyrCollator().compare(cols[1], aSifra) < 0)
					right = mid - 1;
				else {
					// ako su jednaki: to ne moze da se desi ako tabela ima
					// primarni kljuc
					aSifra = (String) getValueAt(mid, 2);
					if (SortUtils.getLatCyrCollator().compare(cols[2], aSifra) > 0)
						left = mid + 1;
					else if (SortUtils.getLatCyrCollator().compare(cols[2],
							aSifra) < 0)
						right = mid - 1;
					else {
						// ako su jednaki: to ne moze da se desi ako tabela ima
						// primarni kljuc
						aSifra = (String) getValueAt(mid, 3);
						if (SortUtils.getLatCyrCollator().compare(cols[3],
								aSifra) > 0)
							left = mid + 1;
						else if (SortUtils.getLatCyrCollator().compare(cols[3],
								aSifra) < 0)
							right = mid - 1;
						else {
							// ako su jednaki: to ne moze da se desi ako tabela
							// ima primarni kljuc
							aSifra = (String) getValueAt(mid, 4);
							if (SortUtils.getLatCyrCollator().compare(cols[4],
									aSifra) > 0)
								left = mid + 1;
							else if (SortUtils.getLatCyrCollator().compare(
									cols[4], aSifra) < 0)
								right = mid - 1;
							else
								break;
						}
					}
				}
			}
		}
		insertRow(left, cols);
		return left;
	}

	@Override
	public int insertRow(String[] cols) throws SQLException {
		int retVal = 0;
		PreparedStatement stmt = DBConnection
				.getConnection()
				.prepareStatement(
						"INSERT INTO STAVKA_PROMETNOG_DOKUMENTA (SIFRA_PREDUZECA, POSLOVNA_GODINA, BROJ_PROMETNOG_DOK, DATUM_NASTANKA_PROM_DOK, "
								+ "REDNI_BROJ_ST_PROM_DOK, SIFRA_GRUPE, SIFRA_ARTIKLA, KOLICINA, CENA, VREDNOST) VALUES (? ,? ,? ,? ,? ,? ,? ,? ,? ,?)");
		stmt.setString(1, cols[0]);
		stmt.setString(2, cols[1]);
		stmt.setString(3, cols[2]);
		stmt.setString(4, cols[3]);
		stmt.setString(5, cols[4]);
		stmt.setString(6, cols[5]);
		stmt.setString(7, cols[6]);
		stmt.setString(8, cols[7]);
		stmt.setString(9, cols[8]);
		stmt.setString(10, cols[9]);
		int rowsAffected = stmt.executeUpdate();
		stmt.close();
		// Unos sloga u bazu
		DBConnection.getConnection().commit();
		if (rowsAffected > 0) {
			// i unos u TableModel
			retVal = sortedInsert(cols);
			fireTableDataChanged();
		}
		return retVal;
	}

	@Override
	public void searchRows(String[] criteria) throws SQLException {
		// TODO Auto-generated method stub
		setRowCount(0);

		boolean first = true;

		if (!criteria[0].equals("")) {
			whereStmt += " WHERE SIFRA_PREDUZECA LIKE '" + criteria[0] + "%'";
			first = false;
		}

		if (!criteria[1].equals("")) {
			if (first) {
				whereStmt += " WHERE POSLOVNA_GODINA LIKE '" + criteria[1]
						+ "%'";
				first = false;
			} else {
				whereStmt += " AND POSLOVNA_GODINA LIKE '" + criteria[1] + "%'";
			}
		}

		if (!criteria[2].equals("")) {
			if (first) {
				whereStmt += " WHERE BOJR_PROMETNOG_DOK LIKE '" + criteria[2]
						+ "%'";
				first = false;
			} else {
				whereStmt += " AND BROJ_PROMETNOG_DOK LIKE '" + criteria[2]
						+ "%'";
			}
		}

		if (!criteria[3].equals("")) {
			if (first) {
				whereStmt += " WHERE DATUM_NASTANKA_PROM_DOK LIKE '"
						+ criteria[3] + "%'";
				first = false;
			} else {
				whereStmt += " AND DATUM_NASTANKA_PROM_DOK LIKE '"
						+ criteria[3] + "%'";
			}
		}

		if (!criteria[4].equals("")) {
			if (first) {
				whereStmt += " WHERE REDNI_BROJ_ST_PROM_DOK LIKE '"
						+ criteria[4] + "%'";
				first = false;
			} else {
				whereStmt += " AND REDNI_BROJ_ST_PROM_DOK LIKE '" + criteria[4]
						+ "%'";
			}
		}

		if (!criteria[5].equals("")) {
			if (first) {
				whereStmt += " WHERE SIFRA_GRUPE LIKE '" + criteria[5] + "%'";
				first = false;
			} else {
				whereStmt += " AND SIFRA_GRUPE LIKE '" + criteria[5] + "%'";
			}
		}

		if (!criteria[6].equals("")) {
			if (first) {
				whereStmt += " WHERE SIFRA_ARTIKLA LIKE '" + criteria[6] + "%'";
				first = false;
			} else {
				whereStmt += " AND SIFRA_ARTIKLA LIKE '" + criteria[6] + "%'";
			}
		}

		if (!criteria[7].equals("")) {
			if (first) {
				whereStmt += " WHERE KOLICINA LIKE '" + criteria[7] + "%'";
				first = false;
			} else {
				whereStmt += " AND KOLICINA LIKE '" + criteria[7] + "%'";
			}
		}

		if (!criteria[8].equals("")) {
			if (first) {
				whereStmt += " WHERE CENA LIKE '" + criteria[8] + "%'";
				first = false;
			} else {
				whereStmt += " AND CENA LIKE '" + criteria[8] + "%'";
			}
		}

		if (!criteria[9].equals("")) {
			if (first) {
				whereStmt += " WHERE VREDNOST LIKE '" + criteria[9] + "%'";
				first = false;
			} else {
				whereStmt += " AND VREDNOST LIKE '" + criteria[9] + "%'";
			}
		}

		String sql = basicQuery + whereStmt + orderBy;

		Statement stmt = DBConnection.getConnection().createStatement();
		ResultSet rset = stmt.executeQuery(sql);
		while (rset.next()) {
			String sifraPreduzeca = rset.getString("SIFRA_PREDUZECA");
			String poslovnaGodina = rset.getString("POSLOVNA_GODINA");
			String brojPrometnogDok = rset.getString("BROJ_PROMETNOG_DOK");
			String datumNastankaPromDok = rset
					.getString("DATUM_NASTANKA_PROM_DOK");
			String redniBrojStPromDok = rset
					.getString("REDNI_BROJ_ST_PROM_DOK");
			String sifraGrupe = rset.getString("SIFRA_GRUPE");
			String sifraArtikla = rset.getString("SIFRA_ARTIKLA");
			String kolicina = rset.getString("KOLICINA");
			String cena = rset.getString("CENA");
			String vrednost = rset.getString("VREDNOST");
			addRow(new String[] { sifraPreduzeca, poslovnaGodina,
					brojPrometnogDok, datumNastankaPromDok, redniBrojStPromDok,
					sifraGrupe, sifraArtikla, kolicina, cena, vrednost });
		}

		whereStmt = "";

		rset.close();
		stmt.close();
		fireTableDataChanged();

	}

	@Override
	public int editRow(String[] cols, int index) throws SQLException {
		// TODO Auto-generated method stub
		int retVal = 0;

		PreparedStatement stmt = DBConnection.getConnection().prepareStatement(
				"UPDATE STAVKA_PROMETNOG_DOKUMENTA SET SIFRA_PREDUZECA = '"
						+ cols[0] + "', POSLOVNA_GODINA = '" + cols[1]
						+ "', BROJ_PROMETNOG_DOK = '" + cols[2]
						+ "', DATUM_NASTANKA_PROM_DOK = '" + cols[3]
						+ "', REDNI_BROJ_ST_PROM_DOK = '" + cols[4]
						+ "', SIFRA_GRUPE = '" + cols[5]
						+ "', SIFRA_ARTIKLA = '" + cols[6] + "', KOLICINA = '"
						+ cols[7] + "', CENA = '" + cols[8] + "', VREDNOST = '"
						+ cols[9] + "' WHERE SIFRA_PREDUZECA = '"
						+ getValueAt(index, 0) + "' AND POSLOVNA_GODINA = '"
						+ getValueAt(index, 1) + "' AND BROJ_PROMETNOG_DOK = '"
						+ getValueAt(index, 2)
						+ "' AND DATUM_NASTANKA_PROM_DOK = '"
						+ getValueAt(index, 3)
						+ "' AND REDNI_BROJ_ST_PROM_DOK = '"
						+ getValueAt(index, 4) + "'");

		int rowsAffected = stmt.executeUpdate();
		stmt.close();
		// Unos sloga u bazu
		DBConnection.getConnection().commit();
		if (rowsAffected > 0) {
			// i unos u TableModel
			removeRow(index);
			retVal = sortedInsert(cols);
			fireTableDataChanged();
		}

		return retVal;
	}

	@Override
	protected boolean checkRow(int index) {
		// TODO Auto-generated method stub
		return false;
	}

}
